use crate::export::Table;
use crate::export::TableColumn;
use crate::export::TableIndex;
use crate::models::AppResult;
use crate::models::DbConnection;
use crate::models::NL;
use crate::utility;
pub use entities::Constraint;
use sqlx::postgres::types::PgMoney;
use sqlx::postgres::types::PgRange;
use sqlx::postgres::PgColumn;
use sqlx::types::Decimal;
use sqlx::types::Uuid;
use sqlx::Column;
use sqlx::Row;
use sqlx::TypeInfo;
use sqlx::{postgres::PgRow, Connection};
use std::io::Write;

mod dbaccess;
mod entities;

#[inline]
async fn get_connection(
    id: &str,
    database: Option<&str>,
) -> AppResult<sqlx::postgres::PgConnection> {
    let conn_data = crate::data::get_connection(id).await?;
    let str_conn = format!(
        "postgres://{}:{}@{}:{}/{}",
        conn_data.username,
        conn_data.password.replace("#", "%32"),
        conn_data.host,
        conn_data.port,
        {
            if let Some(str) = database {
                str
            } else {
                conn_data.database.as_str()
            }
        }
    );
    Ok(sqlx::postgres::PgConnection::connect(&str_conn).await?)
}

#[tauri::command]
pub async fn pgsql_test_connection(
    host: &str,
    port: &str,
    username: &str,
    password: &str,
    database: &str,
) -> AppResult<()> {
    let str_conn = format!(
        "postgres://{}:{}@{}:{}/{}",
        username, password, host, port, database
    );
    let mut conn = sqlx::postgres::PgConnection::connect(&str_conn).await?;
    let sql = "SELECT 1 FROM information_schema.schemata WHERE catalog_name=$1 LIMIT 1";
    _ = sqlx::query(sql).bind(database).fetch_all(&mut conn).await?;
    Ok(())
}

#[tauri::command]
pub async fn pgsql_get_databases(id: &str) -> AppResult<Vec<entities::Database>> {
    // let sql = "SELECT a.oid,a.datname,b.rolname,a.datcollate FROM pg_database a LEFT JOIN pg_authid b ON b.oid = a.datdba WHERE a.datistemplate='f' ORDER BY a.datname";
    let sql = "SELECT a.oid,a.datname,a.datcollate FROM pg_database a WHERE a.datistemplate='f' ORDER BY a.datname";
    let mut conn = get_connection(id, None).await?;
    let datas = sqlx::query(sql).fetch_all(&mut conn).await?;
    let mut result = vec![];
    for data in datas {
        result.push(entities::Database::from_row(data));
    }
    Ok(result)
}

#[tauri::command]
pub async fn pgsql_get_schemas(id: &str, database: &str) -> AppResult<Vec<entities::Schema>> {
    let sql =
        "SELECT schema_name,schema_owner FROM information_schema.schemata WHERE catalog_name=$1 ORDER BY schema_name";
    let mut conn = get_connection(id, Some(database)).await?;
    let datas = sqlx::query(sql).bind(database).fetch_all(&mut conn).await?;
    let mut result = vec![];
    for data in datas {
        result.push(entities::Schema::from_row(data));
    }
    Ok(result)
}

#[tauri::command]
pub async fn pgsql_get_tables(
    id: &str,
    database: &str,
    schema: &str,
) -> AppResult<Vec<entities::Table>> {
    let mut conn = get_connection(id, Some(database)).await?;
    let schema_id = dbaccess::query_schema_id(&mut conn, schema).await?;
    dbaccess::query_tables(&mut conn, &schema_id).await
}

#[tauri::command]
pub async fn pgsql_get_table_columns(
    id: &str,
    database: &str,
    schema: &str,
    table: &str,
) -> AppResult<Vec<entities::TableColumn>> {
    let mut conn = get_connection(id, Some(database)).await?;
    dbaccess::query_table_columns(&mut conn, database, schema, table).await
}

#[tauri::command]
pub async fn pgsql_export_db_structure(id: &str, database: &str, schema: &str) -> AppResult<bool> {
    let path = utility::open_file_dialog(
        "请选择SQL文件保存的路径",
        &format!("{}_{}.sql", database, schema),
    );
    if path.is_none() {
        return Ok(false);
    }
    //获取出schemaId
    let path = path.unwrap();
    let mut conn = get_connection(id, Some(database)).await?;
    let schema_id = dbaccess::query_schema_id(&mut conn, schema).await?;
    let constraints = dbaccess::query_constraints(&mut conn, &schema_id).await?;
    let indexes = dbaccess::query_indexes(&mut conn, schema).await?;
    let structs = dbaccess::query_structs(&mut conn, database, schema, &schema_id).await?;
    let partition_table_names =
        dbaccess::query_partiotion_table_names(&mut conn, &schema_id).await?;
    let mut file = std::fs::OpenOptions::new()
        .create(true)
        .write(true)
        .append(false)
        .open(path)?;
    file.write_all(
        format!(
            "-- Generated At {}",
            chrono::Local::now().format("%Y-%m-%d %H:%M:%S").to_string()
        )
        .as_bytes(),
    )?;
    file.write_all(format!("-- DataBase: {} Schema: {}", database, schema).as_bytes())?;
    for table in map_tables(structs, constraints, indexes) {
        if partition_table_names.contains(&table.name().to_string()) {
            continue;
        }
        file.write_all(table.to_create_sql().as_bytes())?;
    }
    file.flush()?;
    let extensions = dbaccess::query_extensions(&mut conn).await?;
    for row in extensions {
        if row.get::<&str, &str>("name") == "timescaledb" {
            file.write_all(
                format!("{NL}{NL}CREATE EXTENSION IF NOT EXISTS timescaledb;").as_bytes(),
            )?;
            file.write_all(format!("{NL}--hyper tables").as_bytes())?;
            let tables = dbaccess::query_hyper_tables(&mut conn).await?;
            // for i
            for table in tables {
                file.write_all(format!("{NL}{}", table.get_create_sql()).as_bytes())?;
            }
        }
    }
    file.flush()?;
    Ok(true)
}

#[tauri::command]
pub async fn pgsql_export_db_tables(
    id: &str,
    database: &str,
    schema: &str,
    is_sturcture: bool,
    is_data: bool,
    tables: Vec<String>,
) -> AppResult<bool> {
    let path = utility::open_file_dialog(
        "请选择SQL文件保存的路径",
        &format!("{}_{}_tables.sql", database, schema),
    );
    if path.is_none() {
        return Ok(false);
    }
    let path = path.unwrap();
    let mut file = std::fs::OpenOptions::new()
        .create(true)
        .write(true)
        .append(false)
        .open(path)?;
    let mut conn = get_connection(id, Some(database)).await?;
    for table in tables {
        let rows: i64 = sqlx::query(&format!(
            "SELECT COUNT(1) FROM \"{}\".\"{}\"",
            schema, table
        ))
        .fetch_one(&mut conn)
        .await?
        .get(0);
        let mut result = String::new();
        if is_sturcture {
            //导出表结构
        }
        if is_data {
            //导出表数据
            result.push_str(&format!(
                "{NL}-- Table \"{}\".\"{}\" , Record Length = {}{NL}",
                schema, table, rows
            ));
            let buffer = 100;
            let mut offset = 0;
            loop {
                let rows = sqlx::query(&format!(
                    "SELECT * FROM \"{}\".\"{}\" LIMIT {} OFFSET {}",
                    schema, table, buffer, offset
                ))
                .fetch_all(&mut conn)
                .await?;
                export_table_data(schema, &table, &mut result, &rows)?;
                if rows.len() < buffer {
                    break;
                }
                offset += rows.len();
            }
        }
        _ = file.write_all(result.as_bytes());
    }
    Ok(true)
}

#[inline]
fn export_table_data(
    schema: &str,
    table: &str,
    result: &mut String,
    rows: &Vec<PgRow>,
) -> AppResult<()> {
    for row in rows.iter() {
        let mut columns = String::new();
        let mut values = String::new();
        let mut index = 0;
        for column in row.columns() {
            if index != 0 {
                columns.push(',');
                values.push(',');
            }
            index += 1;
            append_cell_value(&mut columns, &mut values, row, column);
        }
        result.push_str(&format!(
            "INSERT INTO \"{}\".\"{}\"({}) VALUES ({});{NL}",
            schema, table, columns, values
        ));
    }
    Ok(())
}

#[tauri::command]
pub async fn pgsql_schema_compare(source: DbConnection, target: DbConnection) -> AppResult<bool> {
    let now = chrono::Local::now().format("%Y-%m-%d-%H-%M-%S");
    let path =
        utility::open_file_dialog("请选择SQL文件保存的路径", &format!("compare_{}.sql", now));
    if path.is_none() {
        return Ok(false);
    }
    let mut conn_source = get_connection(&source.id, Some(&source.database)).await?;
    let schema_id1 = dbaccess::query_schema_id(&mut conn_source, &source.schema).await?;
    let structs1 = dbaccess::query_structs(
        &mut conn_source,
        &source.database,
        &source.schema,
        &schema_id1,
    )
    .await?;
    let constraints1 = dbaccess::query_constraints(&mut conn_source, &schema_id1).await?;
    let indexes1 = dbaccess::query_indexes(&mut conn_source, &source.schema).await?;
    let partition_table_names1 =
        dbaccess::query_partiotion_table_names(&mut conn_source, &schema_id1).await?;
    std::mem::drop(conn_source);
    let tables1 = map_tables(structs1, constraints1, indexes1);
    let mut conn_target = get_connection(&target.id, Some(&target.database)).await?;
    let schema_id2 = dbaccess::query_schema_id(&mut conn_target, &target.schema).await?;
    let structs2 = dbaccess::query_structs(
        &mut conn_target,
        &target.database,
        &target.schema,
        &schema_id2,
    )
    .await?;
    let constraints2 = dbaccess::query_constraints(&mut conn_target, &schema_id2).await?;
    let indexes2 = dbaccess::query_indexes(&mut conn_target, &target.schema).await?;
    let partition_table_names2 =
        dbaccess::query_partiotion_table_names(&mut conn_target, &schema_id2).await?;
    std::mem::drop(conn_target);
    let tables2 = map_tables(structs2, constraints2, indexes2);
    let path = path.unwrap();
    let mut file = std::fs::OpenOptions::new()
        .create(true)
        .write(true)
        .append(false)
        .open(path)?;
    file.write_all(format!("--Auto generate {}", now).as_bytes())?;
    for table1 in &tables1 {
        if partition_table_names1.contains(&table1.name().to_string()) {
            continue;
        }
        if let Some(table2) = find_table(&tables2, table1.name()) {
            file.write_all(table1.to_alter_sql(table2).as_bytes())?;
        } else {
            file.write_all(table1.to_create_sql().as_bytes())?;
        }
    }
    for table2 in tables2 {
        if partition_table_names2.contains(&table2.name().to_string()) {
            continue;
        }
        if find_table(&tables1, table2.name()).is_none() {
            file.write_all(table2.to_drop_sql().as_bytes())?;
        }
    }
    file.flush()?;
    Ok(true)
}

fn find_table<'a: 'b, 'b>(tables: &'a Vec<Table>, table_name: &'b str) -> Option<&'a Table> {
    tables.iter().find(|x| x.name() == table_name)
}

#[inline]
fn find_table_mut<'a: 'b, 'b>(
    tables: &'a mut Vec<Table>,
    table_name: &'b str,
) -> Option<&'a mut Table> {
    tables.into_iter().find(|x| x.name() == table_name)
}

fn find_indexes<'a: 'b, 'b>(indexes: &'a Vec<PgRow>, table_name: &'b str) -> Vec<TableIndex> {
    indexes
        .iter()
        .filter(|x| x.get::<&str, &str>("tablename") == table_name)
        .map(|x| TableIndex::from(x))
        .collect()
}

fn map_tables(
    structs: Vec<PgRow>,
    constraints: Vec<Constraint>,
    indexes: Vec<PgRow>,
) -> Vec<Table> {
    let mut result = vec![];
    for row in structs {
        let table_name = row.get::<&str, &str>("table_name").to_string();
        let table = find_table_mut(&mut result, &table_name);
        match table {
            Some(tb) => {
                tb.push_column(TableColumn::from_row(row));
            }
            None => {
                let mut table = Table::new(
                    row.get("table_oid"),
                    row.get("table_schema"),
                    &table_name,
                    row.get("spcname"),
                );
                table.push_column(TableColumn::from_row(row));
                let oid = table.oid;
                for constraint in constraints.iter().filter(|x| x.rel_id == oid) {
                    table.push_constraint(constraint.clone());
                }
                for index in find_indexes(&indexes, &table_name) {
                    table.push_index(index);
                }
                result.push(table)
            }
        }
    }
    result
}

// #[derive(sqlx::FromRow)]
// #[sqlx(rename = "TEST")]
// struct Test {
//     #[sqlx(rename = "TIME")]
//     time: DateTime<Local>,

//     #[sqlx(rename = "AGENT_ID")]
//     agent_id: String,

//     #[sqlx(rename = "KIND")]
//     kind: i16,

//     #[sqlx(rename = "CONTENT")]
//     content: serde_json::Value,
// }

// #[derive(sqlx::FromRow, sqlx::Type, serde::Serialize, serde::Deserialize)]
// #[sqlx(type_name = "Jsonb")]
// struct Meta {
//     addr: String,
//     email: String,
// }

// #[tauri::command]
// pub async fn test_insert_data(id: &str, kind: i16) -> AppResult<bool> {
//     let mut conn = get_connection(id, Some("eap")).await?;
//     let datas = (0..10000).map(|i| Test {
//         time: chrono::Local::now(),
//         agent_id: i.to_string(),
//         kind,
//         content: serde_json::json!({ "addr": format!("addr{}",i) , "email": format!("email{}@xnebula.com",i),"index":i }),
//     });
//     let mut query_builder: QueryBuilder<Postgres> =
//         QueryBuilder::new("INSERT INTO test(\"TIME\",\"AGENT_ID\",\"KIND\",\"CONTENT\") ");
//     query_builder.push_values(datas, |mut b, data| {
//         b.push_bind(data.time);
//         b.push_bind(data.agent_id);
//         b.push_bind(data.kind);
//         b.push_bind(data.content);
//     });
//     let query = query_builder.build();
//     let result = query.execute(&mut conn).await?;
//     return Ok({
//         if result.rows_affected() > 0 {
//             true
//         } else {
//             false
//         }
//     });
// }

macro_rules! append_value_0 {
    ($values:expr,$row:expr,$type:ty,$column:expr) => {
        if let Some(s) = $row.get::<Option<$type>, &str>($column) {
            $values.push_str(&s.to_string());
        } else {
            $values.push_str("NULL");
        }
    };
}

macro_rules! append_value_1 {
    ($values:expr,$row:expr,$type:ty,$column:expr) => {
        if let Some(s) = $row.get::<Option<$type>, &str>($column) {
            $values.push_str(&format!("'{}'", s.to_string()));
        } else {
            $values.push_str("NULL");
        }
    };
}

macro_rules! append_value_2 {
    ($values:expr,$row:expr,$type:ty,$column:expr) => {
        if let Some(s) = $row.get::<Option<$type>, &str>($column) {
            $values.push_str(&format!("'{}'", s.to_string().replace('\'', "''")));
        } else {
            $values.push_str("NULL");
        }
    };
}

/// array 的处理
macro_rules! append_value_3 {
    ($values:expr,$row:expr,$type:ty,$column:expr,$is_str:expr) => {
        if let Some(s) = $row.get::<Option<Vec<$type>>, &str>($column) {
            $values.push_str(&format!("'{}'", utility::cast_arrry_to_string(s, $is_str)));
        } else {
            $values.push_str("NULL");
        }
    };
}

/// range 的处理
macro_rules! append_value_4 {
    ($values:expr,$row:expr,$type:ty,$column:expr) => {
        if let Some(s) = $row.get::<Option<PgRange<$type>>, &str>($column) {
            $values.push_str(&format!("'{}'", s.to_string()));
        } else {
            $values.push_str("NULL");
        }
    };
}

/// https://docs.rs/sqlx/latest/sqlx/postgres/types/
#[inline]
fn append_cell_value(columns: &mut String, values: &mut String, row: &PgRow, column: &PgColumn) {
    let column_name = column.name();
    columns.push_str(&format!("\"{}\"", column.name()));
    match column.type_info().name() {
        "BOOL" => {
            append_value_0!(values, row, bool, column_name);
        }
        "\"CHAR\"" => {
            append_value_0!(values, row, i8, column_name);
        }
        "INT2" => {
            append_value_0!(values, row, i16, column_name);
        }
        "INT2[]" => {
            append_value_3!(values, row, i16, column_name, false);
        }
        "INT4" => {
            append_value_0!(values, row, i32, column_name);
        }
        "INT4[]" => {
            append_value_3!(values, row, i32, column_name, false);
        }
        "INT8" => {
            append_value_0!(values, row, i64, column_name);
        }
        "INT8[]" => {
            append_value_3!(values, row, i64, column_name, false);
        }
        "TEXT" | "VARCHAR" | "CHAR(N)" | "NAME" => {
            append_value_2!(values, row, String, column_name);
        }
        "TEXT[]" => {
            append_value_3!(values, row, String, column_name, true);
        }
        "INT4RANGE" => {
            append_value_4!(values, row, i32, column_name);
        }
        "INT8RANGE" => {
            append_value_4!(values, row, i64, column_name);
        }
        "TSRANGE" => {
            append_value_4!(values, row, chrono::NaiveDateTime, column_name);
        }
        "TSTZRANGE" => {
            append_value_4!(values, row, chrono::DateTime<chrono::Utc>, column_name);
        }
        "DATERANGE" => {
            append_value_4!(values, row, chrono::NaiveDate, column_name);
        }
        "NUMRANGE" => {
            append_value_4!(values, row, Decimal, column_name);
        }
        "NUMERIC" => {
            append_value_0!(values, row, Decimal, column_name);
        }
        "MONEY" => {
            if let Some(s) = row.get::<Option<PgMoney>, &str>(column_name) {
                values.push_str(&s.to_decimal(2).to_string());
            } else {
                values.push_str("NULL");
            }
        }
        "UUID" => {
            append_value_1!(values, row, Uuid, column_name);
        }
        "TIMESTAMP" => {
            if let Some(s) = row.get::<Option<chrono::NaiveDateTime>, &str>(column_name) {
                values.push_str(&format!("'{}'", s.format("%Y-%m-%d %H:%M:%S.%6f")));
            } else {
                values.push_str("NULL");
            }
        }
        "TIMESTAMPTZ" => {
            if let Some(s) = row.get::<Option<chrono::DateTime<chrono::Utc>>, &str>(column_name) {
                values.push_str(&format!("'{}'", s.format("%Y-%m-%d %H:%M:%S.%6f")));
            } else {
                values.push_str("NULL");
            }
        }
        "DATE" => {
            if let Some(s) = row.get::<Option<chrono::NaiveDate>, &str>(column_name) {
                values.push_str(&format!("'{}'", s.format("%Y-%m-%d")));
            } else {
                values.push_str("NULL");
            }
        }
        "TIME" => {
            if let Some(s) = row.get::<Option<chrono::NaiveTime>, &str>(column_name) {
                values.push_str(&format!("'{}'", s.format("%H:%M:%S.%6f")));
            } else {
                values.push_str("NULL");
            }
        }
        "JSON" | "JSONB" => {
            if let Some(s) = row.get::<Option<serde_json::Value>, &str>(column_name) {
                values.push_str(&format!("'{}'", s.to_string().replace('\'', "''")));
            } else {
                values.push_str("NULL");
            }
        }
        "BIT" | "VARBIT" => {
            if let Some(s) = row.get::<Option<sqlx::types::BitVec>, &str>(column_name) {
                if let Ok(str) = String::from_utf8(s.to_bytes()) {
                    values.push_str(&format!("'{}'", str));
                } else {
                    values.push_str("NULL");
                }
            } else {
                values.push_str("NULL");
            }
        }
        _ => {
            // 不支持 BYTEA
            values.push_str(&format!(
                "'unsupport column type : {}'",
                column.type_info().name()
            ));
        }
    }
}
